package com.cskaoyan.mall.dao.implement;

import com.cskaoyan.mall.dao.GoodsDao;
import com.cskaoyan.mall.model.bo.replyBO;
import com.cskaoyan.mall.model.sql.Goods;
import com.cskaoyan.mall.model.sql.Type;
import com.cskaoyan.mall.model.vo.*;
import com.cskaoyan.mall.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.List;

public class GoodsDaoImpl implements GoodsDao {

    QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());

    @Override
    public List<Type> getType() {
        List<Type> typeList = null;
        try {
            typeList = runner.query("select * from type", new BeanListHandler<Type>(Type.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return typeList;
    }

    @Override
    public List<TypeGoodsVO> getGoodsType(String typeId) {
        List<TypeGoodsVO> goodsVOList = null;
        try {
            String sql="select id,img,name,price,typeId,stockNum from goods where 1 = 1";
            if(!"-1".equals(typeId)){
                sql+=" and typeId = ?";
                goodsVOList = runner.query(sql,
                        new BeanListHandler<TypeGoodsVO>(TypeGoodsVO.class),
                        typeId);
            }else {
                goodsVOList = runner.query(sql,
                        new BeanListHandler<TypeGoodsVO>(TypeGoodsVO.class));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsVOList;
    }

    @Override
    public void addGoods(Goods goods) {
        try {
            //返回值表示影响的行数，不是取的ID值
            int update = runner.update("insert into goods values (null,?,?,?,?,?,?)",
                    goods.getImg(),
                    goods.getName(),
                    goods.getPrice(),
                    goods.getTypeId(),
                    goods.getStockNum(),
                    goods.getDesc()
            );
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public int lastInsertId() {
            BigInteger query = null;
        try {
            query = (BigInteger) runner.query("select last_insert_id()", new ScalarHandler<>());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query.intValue();
    }

    @Override
    public void addSpecs(List<Spec> specs) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        for (Spec spec: specs
             ) {
            try {
                queryRunner.update("insert into spec(specName,stockNum,unitPrice,goodsid) values (?,?,?,?)",
                        spec.getSpecName(),
                        spec.getStockNum(),
                        spec.getUnitPrice(),
                        spec.getGoodsId());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public InfoGoodVO getInfoGoodVO(String id) {
        InfoGoodVO infoGoodVO = null;
        try {
            infoGoodVO = runner.query( "select id,img,name,"+"`"+"desc"+"`"+",typeId from goods where id = ?",
                    new BeanHandler<InfoGoodVO>(InfoGoodVO.class),
                    id);
            infoGoodVO.setUnitPrice(0.0);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return infoGoodVO;
    }

    @Override
    public List<SpecVO> getSpecVO(String goodsid) {
        List<SpecVO> specVOList = null;
        try {
            specVOList = runner.query("select id,specName,stockNum,unitPrice from spec where goodsid = ?",
                    new BeanListHandler<>(SpecVO.class),
                    goodsid);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return specVOList;
    }

    @Override
    public void deleteGoods(String id) {
        try {
            int update = runner.update("delete from goods where id = ?", id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteSpec(String goodsid) {
        try {
            int update = runner.update("delete from spec where goodsid = ?", goodsid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void addType(Type type) {
        String name = type.getName();
        try {
            int update = runner.update("insert into type(name) value(?)", name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteType(String id) {
        try {
            int update = runner.update("delete from type where id = ?", id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<noReplyMsgVO> noReplyMsg(List<noReplyMsgVO> noReplyMsgVOS) {
        List<noReplyMsgVO> replyMsgVOS = null;
        try {
            replyMsgVOS = runner.query("select id,userId,goodsId,content,state,createtime,goodsName,userName from reply where" +
                    " state = ?", new BeanListHandler<noReplyMsgVO>(noReplyMsgVO.class), 0);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return replyMsgVOS;
    }

    @Override
    public List<noReplyMsgVO> ReplyMsg(List<noReplyMsgVO> noReplyMsgVOS) {
        List<noReplyMsgVO> replyMsgVOS = null;
        try {
            replyMsgVOS = runner.query("select id,userId,goodsId,content,replyContent,state,createtime,goodsName,userName from reply where" +
                    " state = ?", new BeanListHandler<noReplyMsgVO>(noReplyMsgVO.class), 1);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return replyMsgVOS;
    }

    @Override
    public void reply(replyBO replybo) {
        try {
            int update = runner.update("update reply set state = 1,replyContent = ? where id = ?", replybo.getContent(), replybo.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Goods> searchGoods(String keyword) {
        List<Goods> query = null;
        try {
            query = runner.query("select id,img,name,price,typeId from goods where name like ?"
                    , new BeanListHandler<Goods>(Goods.class), "%" + keyword + "%");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }
}
